from sqlobject import *
import datetime

sqlhub.processConnection = connectionForURI(
    "mysql://weibo:c747917975@ecs.e1xp.top:3306/weibospiderdata?charset=utf8")#


class Tag(SQLObject):
    """
        Tag类对应的SQLObject Mapper
    """
    tagName = StringCol()


class Topic(SQLObject):
    """
        Topic类对应的SQLObject Mapper
    """
    topicName = StringCol()
    topicDes = StringCol()
    tagId = IntCol()
    topicHost = StringCol()
    processFlag = IntCol()

    def _set_processFlag(self, value: int):
        self._SO_set_processFlag(value)


class RawData(SQLObject):
    """
        RawData类对应的SQLObject Mapper
    """
    catchTime = DateTimeCol()
    ranking = IntCol()
    readCount = IntCol()
    topicId = IntCol()
    processFlag = IntCol()

    def _set_processFlag(self, value: int):
        self._SO_set_processFlag(value)


class TagCount(SQLObject):
    """
        Tag统计类对应的SQLObject Mapper
    """
    tagId = IntCol()
    tagCount = IntCol()
    processTime = DateTimeCol()


class TopicToWord(SQLObject):
    """
        话题与名词间表对应的SQLObject Mapper
    """
    topicId = IntCol()
    wordId = IntCol()


class Word(SQLObject):
    """
        名词类对应的SQLObject Mapper
    """
    wordName = StringCol()


class WordCount(SQLObject):
    """
        名词统计类的对应SQLObject Mapper
    """
    wordId = IntCol()
    wordCount = IntCol()
    processTime = DateTimeCol()


class TopicCount(SQLObject):
    """
        话题统计对应的SQLObject Mapper
    """
    topicId = IntCol()
    topicCount = IntCol()
    processTime = DateTimeCol()


class TagMapper:
    """
    TagMapper类
    """

    def __init__(self):
        """
        初始化函数
        """

    def addTag(self, tagName: str) -> int:
        """
        用于添加Tag
        :param tagName:     插入的tag名字
        :return: 返还tag id
        """
        tag = Tag(tagName=tagName)
        return tag.id

    def allTag(self) -> list:
        """
        获取数据表中所有tag
        :return: 返还tag list
        """
        return list(Tag.select())

    def getTagById(self, tagId: int) -> Tag:
        """
        通过id获取标签
        :param tagId:       查询的标签id
        :return: 返还tag对象
        """
        return Tag.get(tagId)

    def getTagByName(self, name: str) -> int:
        """
        通过名字获取标签
        :param name:        查询的标签名字
        :return: 返还tag对象
        """
        return Tag.select(Tag.q.tagName == name).getOne().id

    def isTag(self, tagName: str) -> int:
        """
        查询tag是否存在
        :param tagName:     查询的tag名称
        :return: 存在返还1，不存在返还0
        """
        if Tag.select(Tag.q.tagName == tagName).count() > 0:
            return 1
        else:
            return 0


class TopicMapper:
    """
    TopicMapper类
    """

    def __init__(self):
        """
            初始化函数
            """

    def addTopic(self, topicName: str, topicDes: str, tagId: int, topicHost: str) -> int:
        """
        用于插入Topic条目
        :param topicName:   话题名
        :param topicDes:    话题描述
        :param tagId:       对应标签号
        :param topicHost:   对应主持人
        :return: 返还话题id
        """
        topic = Topic(topicName=topicName, topicDes=topicDes, tagId=tagId, topicHost=topicHost, processFlag=0)
        return topic.id

    def allTopic(self) -> list:
        """
        获取所有话题条目
        :return: 返还话题条目列表
        """
        return list(Topic.select())

    def getTopicByKey(self, topicId: int) -> Topic:
        """
        通过话题号查询话题条目
        :param topicId:     话题号
        :return: 返还话题条目
        """
        return Topic.get(topicId)

    def getTopicByName(self, topicName: str) -> int:
        """
        通过话题名称查询话题条目
        :param topicName:   话题名称
        :return: 返还话题条目
        """
        return Topic.select(Topic.q.topicName == topicName).getOne().id

    def isTopic(self, topicName: str) -> int:
        """
        查询话题是否存在
        :param topicName:   查询的话题名
        :return: 存在返还1，不存在返还0
        """
        if Topic.select(Topic.q.topicName == topicName).count() > 0:
            return 1
        else:
            return 0

    def updateProcessFlag(self, id: int, value: int):
        """
        修改给定话题id的处理标识符
        :param id:      话题id
        :param value:   标识符修改值
        :return: 无返还
        """
        topic = Topic.get(id)
        topic._set_processFlag(value)

    def getUnparseTopic(self, startTime: datetime, endTime: datetime) -> list:
        """
        获取时间区间之间的
        :param startTime:   查询起始时间
        :param endTime:     查询结束时间
        :return:  返还未解析数据列表
        """
        unparseTopic = Topic.select(Topic.q.processFlag == 0)
        return list(unparseTopic)


class RawDataMapper:
    """
        RawDataMapper类
    """

    def __init__(self):
        """
            初始化函数
            """

    def getRawData(self, startTime: datetime, endTime: datetime) -> list:
        """
        用于获取爬取数据，支持起止时间
        :param startTime:   查询起始时间
        :param endTime:     查询结束时间
        :return: 返还原始数据条目
        """
        result = RawData.selectBy().filter(startTime <= RawData.q.catchTime).filter(RawData.q.catchTime <= endTime)
        return list(result)

    def addRawData(self, catchTime: datetime, ranking: int, readCount: int, topicId: int) -> int:
        """
        添加爬取数据
        :param catchTime:   爬取时间
        :param ranking:     话题榜顺序
        :param readCount:   阅读量
        :param topicId:     话题序号（对应话题实体表）
        :return: 数据id
        """
        rawdata = RawData(catchTime=catchTime, ranking=ranking, readCount=readCount, topicId=topicId, processFlag=0)
        return rawdata.id

    def updateRawData(self, id: int, value: int):
        """
        修改给定原始数据id的处理标识符
        :param id:      原始数据id
        :param value:   标识符修改值
        :return: 无返还
        """
        rawdata = RawData.get(id)
        rawdata._set_processFlag(value)

    def getUnparseRawData(self, starTime: datetime, endTime: datetime) -> list:
        """
        用于获取时间段内的未处理数据
        :param starTime: 开始时间
        :param endTime:  结束时间
        :return: 返还时间段内的未处理数据
        """
        unparseData = RawData.select(RawData.q.processFlag == 0).filter(starTime < RawData.q.catchTime).filter(
            RawData.q.catchTime < endTime)
        return list(unparseData)


class TagCountMapper:
    """
        TagCount的Mapper类
    """

    def addTagCount(self, tagId: int, tagCount: int, processTime: datetime) -> int:
        """
        添加一条增量统计数据
        :param tagId:       对应tag Id
        :param tagCount:    tag统计数
        :param processTime: 处理时间
        :return: 返还统计条目id
        """
        tagCount = TagCount(tagId=tagId, tagCount=tagCount, processTime=processTime)
        return tagCount.id

    def getTagCount(self, startTime: datetime, endTime: datetime) -> list:
        """
        用于查询时间区间内的tag统计
        :param startTime:    起始时间
        :param endTime:     结束时间
        :return: 返还区间内tag统计情况
        """
        result = TagCount.selectBy().filter(startTime <= TagCount.q.processTime).filter(
            TagCount.q.processTime <= endTime)
        return list(result)

    def countRow(self) -> int:
        """
        统计表中条目
        :return: 返还条目数
        """
        return TagCount.selectBy().count()


class TopicToWordMapper:
    """
        Topic与Word的连接表Mapper
    """

    def getAll(self) -> list:
        return list(TopicToWord.select())

    def addTopicToWord(self, topicId: int, wordId: int) -> int:
        """
        添加话题与名词的映射
        :param topicId:     话题id
        :param wordId:      名词id
        :return: 返还条目主键id
        """
        topicToWord = TopicToWord(topicId=topicId, wordId=wordId)
        return topicToWord.id

    def getByTopicid(self, topicId: int) -> list:
        """
        查询话题对应的名词
        :param topicId:     话题id
        :return: 返还对应名词id列表
        """
        return list(TopicToWord.select(TopicToWord.q.topicId == topicId))

    def getByWordId(self, wordId: int) -> list:
        """
        查询名词所来自的话题
        :param wordId:      名词id
        :return: 返还对应的话题id列表
        """
        return list(TopicToWord.select(TopicToWord.q.wordId == wordId))


class WordMapper:
    """
        名词实体Mapper
    """

    def getAll(self) -> list:
        """
        获取全部词语
        :return: 返还全部词语List
        """
        return list(Word.select())

    def addWord(self, wordName: str) -> int:
        """
        添加名词
        :param wordName:    名词
        :return: 返还名词id
        """
        word = Word(wordName=wordName)
        return word.id

    def getWordByid(self, id: int) -> Word:
        """
        通过wordid查询word
        :param id:          word Id
        :return: 返还对应的word类
        """
        word = Word.get(id)
        return word


class WordCountMapper:
    """
        名词统计Mapper
    """

    def addWordCount(self, wordId: int, wordCount: int, processTime: datetime) -> int:
        """
        添加名词统计条目
        :param wordId:      名词id
        :param wordCount:       名词统计
        :param processTime: 统计时间
        :return: 返还条目的id
        """
        wordCount = WordCount(wordId=wordId, wordCount=wordCount, processTime=processTime)
        return wordCount.id

    def getWordCount(self, startTime: datetime, endTime: datetime) -> list:
        """
        查询时间段内名词统计
        :param startTime:   起始时间
        :param endTime:     结束时间
        :return: 返还统计的list
        """
        wordCount = WordCount.selectBy().filter(startTime <= WordCount.q.processTime).filter(
            WordCount.q.processTime <= endTime)
        return list(wordCount)

    def countRow(self) -> int:
        """
        统计表中条目
        :return: 返还条目数
        """
        return WordCount.selectBy().count()


class TopicCountMapper:
    """
        话题统计Mapper
    """

    def addTopicCount(self, topicId: int, topicCount: int, processTime: datetime) -> int:
        """
        添加话题统计条目
        :param topicId:     话题id
        :param topicCount:       计数
        :param processTime: 处理时间
        :return: 返还添加的条目id
        """
        topicCount = TopicCount(topicId=topicId, topicCount=topicCount, processTime=processTime)
        return topicCount.id

    def getTopicCount(self, startTime: datetime, endTime: datetime) -> list:
        """
        通过
        :param startTime:   查询开始时间
        :param endTime:     查询结束时间
        :return:    返还topicCount List
        """
        topicCount = TopicCount.selectBy().filter(startTime <= TopicCount.q.processTime).filter(
            TopicCount.q.processTime <= endTime)
        return list(topicCount)

    def countRow(self) -> int:
        """
        统计表中条目
        :return: 返还条目数
        """
        return TopicCount.selectBy().count()
